package com.infore.maintenance.main.service.impl;

import cn.hutool.json.JSONUtil;
import com.baomidou.mybatisplus.service.impl.ServiceImpl;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.infore.common.annotation.RecordLog;
import com.infore.common.constants.DataStatConstants;
import com.infore.common.enums.ExecuteSql;
import com.infore.common.exception.MyException;
import com.infore.common.model.dto.LineDataDto;
import com.infore.common.model.entity.TBaseQuerySql;
import com.infore.common.request.query.CheckSqlRequest;
import com.infore.common.request.query.QuerySqlRequest;
import com.infore.common.response.ApiEntityResponse;
import com.infore.common.response.ApiListResponse;
import com.infore.common.response.ApiPageResponse;
import com.infore.common.utils.EncryptAESUtil;
import com.infore.common.utils.StringUtils;
import com.infore.maintenance.main.dao.TBaseQuerySqlMapper;
import com.infore.maintenance.main.service.TBaseQuerySqlService;
import com.infore.maintenance.main.utils.RedisUtil;
import com.infore.maintenance.main.utils.parsesql.SqlAnalysis;
import com.infore.maintenance.main.utils.parsesql.TextSqlNode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>
 * 查询接口SQL模板 服务实现类
 * </p>
 *
 * @author lijh
 * @since 2022-12-01
 */
@Service
@RecordLog(descript = "数据查询模板操作")
public class TBaseQuerySqlServiceImpl extends ServiceImpl<TBaseQuerySqlMapper, TBaseQuerySql> implements TBaseQuerySqlService {

    @Autowired
    private RedisUtil redisUtil;

    @Autowired
    private TBaseQuerySqlMapper sqlMapper;



    /**
     * 获取sql模板
     * @param sqlCode
     * @return
     */

    @Override
    public TBaseQuerySql getQuerySql(String sqlCode){
        TBaseQuerySql querySqlVo = null;
        if(!redisUtil.hasKey(sqlCode)){
            querySqlVo = sqlMapper.queryBySqlCode(sqlCode);
            redisUtil.set(sqlCode,JSONUtil.toJsonStr(querySqlVo) );
        }else{
            querySqlVo = JSONUtil.toBean(redisUtil.get(sqlCode).toString(), TBaseQuerySql.class);
        }
        return querySqlVo;
    }

    /**
     * SQL解密与防止SQL注入校验
     * @param encryptSql
     * @return
     */
    @Override
    public String checkSql(String encryptSql){
        String executeSql=null;
        try {
            // 解密
            executeSql = EncryptAESUtil.aesDecrypt(encryptSql,DataStatConstants.AES_KEY_QUERY);
        } catch (Exception e) {
            e.printStackTrace();
            throw new MyException("500","AES执行SQL解密失败");
        }
        if(StringUtils.isEmpty(executeSql)){
            throw new MyException("500","执行SQL不能为空");
        }
        //防sql注入
        SqlAnalysis.preventSqlInjection(executeSql, ExecuteSql.SELECT);
        return executeSql;
    }

    @Override
    public boolean checkSqlExecute(TBaseQuerySql tbasequerysql) {
        Map<String, Object> parameters = new HashMap<>();
        Map varMap= JSONUtil.parseObj(tbasequerysql.getQueryJson());
        String executeSql =  TextSqlNode.parseSqlParamenters(tbasequerysql.getExecuteSql(),varMap,parameters);
        try {
            PageHelper.startPage(1, 1);
            sqlMapper.queryBySqlAndParameters(executeSql, parameters);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常，请检查执行sql与json参数:"+tbasequerysql.getExecuteSql(),e.toString());
        }
        return true;
    }
    /**
     * 校验sql并返回结果
     * @param request
     * @param page
     * @return
     */
    @Override
    public ApiPageResponse<Map<String, Object>> checkSqlResult(CheckSqlRequest request, Page page) {
        String executeSql = checkSql(request.getEncryptSql());

        Map<String, Object> parameters = new HashMap<>();
        Map varMap= JSONUtil.parseObj(request.getJsonParam());
        executeSql =  TextSqlNode.parseSqlParamenters(executeSql,varMap,parameters);

        List<Map<String, Object>> list = null;
        try {
            list = sqlMapper.queryBySqlAndParameters(executeSql, parameters);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常",e.toString());
        }
        //列头
        List<String> head = SqlAnalysis.analyzeSelectSQL(executeSql);
        if(head.size()==1 && head.get(0).contains("*")){
            head = new ArrayList<>();
            if(list!=null){
                Map<String, Object> map = list.get(0);
                for (Map.Entry<String,Object> entry : map.entrySet()){
                    head.add(entry.getKey());
                }
            }
        }
        return ApiPageResponse.ok(list,page,executeSql,head);
    }

    /**
     * 分页数据
     * @param request
     * @return
     */
    @Override
    public ApiPageResponse<Map<String, Object>> queryDataByCxpzForPage(QuerySqlRequest request) {
        TBaseQuerySql querySqlVo = getQuerySql(request.getSqlCode());
        List<String> head = SqlAnalysis.getHead(querySqlVo.getResultColumn());
        Map<String, Object> parameters = new HashMap<>();
        Map varMap= JSONUtil.parseObj(request.getJsonParam());
        String executeSql =  TextSqlNode.parseSqlParamenters(querySqlVo.getExecuteSql(),varMap,parameters);
        System.out.println(executeSql);
        System.out.println(JSONUtil.toJsonStr(parameters));

        List<Map<String, Object>> list = null;
        Page<Object> page = null;
        try {
            page = PageHelper.startPage(request.getPage(), request.getLimit());
            list = sqlMapper.queryBySqlAndParameters(executeSql, parameters);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常",e.toString());
        }
        return ApiPageResponse.ok(list,page,executeSql,head);
    }


    @Override
    public Object queryDataByCxpz(QuerySqlRequest request) {
        TBaseQuerySql querySqlVo = getQuerySql(request.getSqlCode());
        Map<String, Object> parameters = new HashMap<>();
        Map varMap= JSONUtil.parseObj(request.getJsonParam());
        String executeSql =  TextSqlNode.parseSqlParamenters(querySqlVo.getExecuteSql(),varMap,parameters);
        if(querySqlVo.getResultLimit()!=null && querySqlVo.getResultLimit()>0){
            PageHelper.startPage(1,querySqlVo.getResultLimit(),false);
        }
        List<Map<String, Object>> list = null;
        try {
            list = sqlMapper.queryBySqlAndParameters(executeSql, parameters);
        } catch (Exception e) {
            throw new MyException("500","SQL执行异常",e.toString());
        }
//        System.out.println(JSONUtil.toJsonStr(list));
        //把需要用到的格式都考虑进来
        String dataFormat = request.getDataFormat();

        if(StringUtils.isEmpty(dataFormat)){
            dataFormat = querySqlVo.getResultDataformat();
        }
        if("TABLE".equalsIgnoreCase(dataFormat)){
            System.out.println("把数据组装成列表格式");
            return  ApiListResponse.ok(list,executeSql,SqlAnalysis.getHead(querySqlVo.getResultColumn()));
        }else if("PIE".equals(dataFormat.toUpperCase())){

            System.out.println("把数据组装成饼图格式");
        }else if("LINE".equalsIgnoreCase(dataFormat)){
            LineDataDto dto = new LineDataDto();
            List<Object> xAxis = new ArrayList<>();
            List<Object> yAxis = new ArrayList<>();
            for (Map<String, Object> row:list) {
                xAxis.add(row.get("xAxis"));
                yAxis.add(row.get("yAxis"));
            }
            dto.setXAxis(xAxis);
            dto.setYAxis(yAxis);
            //获取单位
            String resultUnit = querySqlVo.getResultUnit();
            if(StringUtils.isNotEmpty(resultUnit)){
                Map<String, String> unitMap = SqlAnalysis.getUnit(resultUnit);
                dto.setUnit(unitMap.get("yAxis"));
            }
            System.out.println("把数据组装成折线图格式");
            return ApiEntityResponse.ok(dto,executeSql);
        }else if("MAP".equalsIgnoreCase(dataFormat)){
            System.out.println("把数据组装成地图格式");
            return null;
        }
        return  ApiListResponse.ok(list,executeSql,SqlAnalysis.getHead(querySqlVo.getResultColumn()));
    }


}
